﻿Imports System.Data.OleDb

Public Class SiteUserGateway
    Inherits DataGateway

    Protected Overrides ReadOnly Property TableName() As String
        Get
            Return "SiteUser"
        End Get
    End Property

    Public Function Exists(ByVal userName As String) As Boolean
        Return FindByUserName(userName).Rows.Count() = 1
    End Function

    Public Function FindByUserName(ByVal userName As String) As DataTable
        Dim sqlQuery As String = String.Format("SELECT * FROM {0} WHERE UserName=@UserName", TableName)
        Dim queryParams As New Hashtable()
        queryParams.Add("@UserName", userName)
        Return QueryTable(sqlQuery, queryParams)
    End Function

    Public Function Insert(ByVal userName As String, Optional ByVal firstName As String = "", Optional ByVal lastName As String = "", Optional ByVal photo As String = "person.png")
        ' TODO: extract common logic in base class
        Dim sqlQuery As String = String.Format("INSERT INTO {0} (UserName, FirstName, LastName, Photo) VALUES (@UserName, @FirstName, @LastName, @Photo)", TableName)
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@UserName", userName)
                command.Parameters.AddWithValue("@FirstName", firstName)
                command.Parameters.AddWithValue("@LastName", lastName)
                command.Parameters.AddWithValue("@Photo", photo)
                connection.Open()
                command.ExecuteNonQuery()
            End Using
        End Using

        ' Get last inserted ID
        Dim sqlIdQuery As String = "SELECT @@IDENTITY"
        Dim newId As Integer
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlIdQuery, connection)
                connection.Open()
                newId = command.ExecuteScalar()
            End Using
        End Using
        Return newId
    End Function

    Public Sub Update(ByVal userName As String, ByVal firstName As String, ByVal lastName As String, Optional ByVal photo As String = Nothing)
        Dim sqlQuery As String
        If photo Is Nothing Then
            sqlQuery = String.Format("UPDATE {0} SET FirstName=@FirstName, LastName=@LastName WHERE UserName=@UserName", TableName)
        Else
            sqlQuery = String.Format("UPDATE {0} SET FirstName=@FirstName, LastName=@LastName, Photo=@Photo WHERE UserName=@UserName", TableName)
        End If
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@FirstName", firstName)
                command.Parameters.AddWithValue("@LastName", lastName)
                If photo IsNot Nothing Then
                    command.Parameters.AddWithValue("@Photo", photo)
                End If
                command.Parameters.AddWithValue("@UserName", userName)
                connection.Open()
                command.ExecuteNonQuery()
            End Using
        End Using
    End Sub
End Class